﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using MiniCMS.Model;
using CarolLib;
using System.Data;
using System.Data.OleDb;
using System.Windows.Forms;

namespace MiniCMS.Business
{
    public class OleDbDataProvider
    {
        private static readonly string SQL_QueryCustomerInfo = "SELECT * FROM CustomerInfo ";

        private static string conn = MiniCMS.Configuration.AppSettings.GetValue<string>("conn") + Application.StartupPath + MiniCMS.Configuration.AppSettings.GetValue<string>("DBstr");

        #region SystemUser

        public static SystemUser Login(string account, string pwd)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("SELECT ID,UserName,Password,FullName,PowerFlag ");
            strSql.AppendFormat("FROM SystemUser WHERE UserName='{0}' AND Password='{1}' ", account, pwd);

            using (var reader = OleDbHelper.ExecuteReader(conn, System.Data.CommandType.Text, strSql.ToString()))
            {
                if (reader.HasRows)
                {
                    return DTOHelper.CreateModel<SystemUser>(reader);
                }
                else
                {
                    return null;
                }
            }
        }

        public static bool QueryAccountExist(string account)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.AppendFormat("SELECT ID FROM SystemUser WHERE UserName='{}'", account);

            using (var reader =OleDbHelper.ExecuteReader(conn, System.Data.CommandType.Text, strSql.ToString()))
            {
                return reader.HasRows;
            }
        }

        public static int InsertSystemUser(SystemUser su)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("INSERT INTO SystemUser([UserName],[Password],[FullName],[PowerFlag]) ");
            strSql.AppendFormat("VALUES ('{0}','{1}','{2}',{3})", su.UserName, su.Password, su.FullName, su.PowerFlag.ToString());

            return OleDbHelper.ExecuteNonQuery(conn, strSql.ToString());
        }

        public static int UpdateSystemUser(SystemUser su)
        {
            string sql = string.Format("Update SystemUser SET [Password]='{1}',[FullName]='{2}',[PowerFlag]={3} where [UserName]='{0}' ", su.UserName, su.Password, su.FullName, su.PowerFlag);
            return OleDbHelper.ExecuteNonQuery(conn, sql);
        }

        public static List<SystemUser> GetAllSystemUsers()
        {
            using (var reader = OleDbHelper.ExecuteReader(conn, System.Data.CommandType.Text, "select * from SystemUser"))
            {
                if (reader.HasRows)
                {
                    return DTOHelper.CreateModelList<SystemUser>(reader);
                }
                else
                {
                    return new List<SystemUser>();
                }
            }
        }

        public static int DeleteSystemUser(string userName)
        {
            string sql = string.Format("delete from SystemUser where [UserName]='{0}'", userName);
            return OleDbHelper.ExecuteNonQuery(conn, sql);
        }

        #endregion

        #region CustomerInfo

        public static int DeleteCustomerInfo(string customerNo)
        {
            string sql = string.Format("delete from CustomerInfo where 客户编号={0}", customerNo);
            return OleDbHelper.ExecuteNonQuery(conn, sql);
        }

        public static List<CustomerInfo> QueryCustomerInfo(string where)
        {
            try
            {
                using (var reader = OleDbHelper.ExecuteReader(conn, System.Data.CommandType.Text, SQL_QueryCustomerInfo + where))
                {
                    if (reader.HasRows)
                    {
                        return DTOHelper.CreateModelList<CustomerInfo>(reader);
                    }
                    else
                    {
                        return new List<CustomerInfo>();
                    }
                }
            }
            catch
            {
                throw;
            }
        }

        public static DataTable QueryCustomerInfoTable(string where)
        {
            try
            {
                return OleDbHelper.ExecuteDataset(conn, CommandType.Text, SQL_QueryCustomerInfo + where).Tables[0];
            }
            catch
            {
                throw;
            }
        }

        public static int InsertCustomerInfo(MiniCMS.Model.CustomerInfo model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("insert into CustomerInfo(");
            strSql.Append("客户编号,客户姓名,性别,出生日期,联系电话,手机号码,联系地址,学历,职业,开户日期,注销日期,Email,QQ,MSN,投资品种,交易类型,交易风格,持仓情况,连带账户,家庭实际地址,家庭财产情况,家庭成员,性格特征,爱好,客户需求,理财意识、风险偏好,社会关系,已经享受的服务,实际操作人,经纪人,服务渠道,备注,档案修改时间,制表人,复核状态");
            strSql.Append(")");
            strSql.Append(" values (");
            strSql.Append("" + model.客户编号 + ",");
            strSql.Append("'" + model.客户姓名 + "',");
            strSql.Append("'" + model.性别 + "',");
            strSql.Append("'" + model.出生日期 + "',");
            strSql.Append("'" + model.联系电话 + "',");
            strSql.Append("'" + model.手机号码 + "',");
            strSql.Append("'" + model.联系地址 + "',");
            strSql.Append("'" + model.学历 + "',");
            strSql.Append("'" + model.职业 + "',");
            strSql.Append("'" + model.开户日期 + "',");
            strSql.Append("'" + model.注销日期 + "',");
            strSql.Append("'" + model.Email + "',");
            strSql.Append("'" + model.QQ + "',");
            strSql.Append("'" + model.MSN + "',");
            strSql.Append("'" + model.投资品种 + "',");
            strSql.Append("'" + model.交易类型 + "',");
            strSql.Append("'" + model.交易风格 + "',");
            strSql.Append("'" + model.持仓情况 + "',");
            strSql.Append("'" + model.连带账户 + "',");
            strSql.Append("'" + model.家庭实际地址 + "',");
            strSql.Append("'" + model.家庭财产情况 + "',");
            strSql.Append("'" + model.家庭成员 + "',");
            strSql.Append("'" + model.性格特征 + "',");
            strSql.Append("'" + model.爱好 + "',");
            strSql.Append("'" + model.客户需求 + "',");
            strSql.Append("'" + model.理财意识风险偏好 + "',");
            strSql.Append("'" + model.社会关系 + "',");
            strSql.Append("'" + model.已经享受的服务 + "',");
            strSql.Append("'" + model.实际操作人 + "',");
            strSql.Append("'" + model.经纪人 + "',");
            strSql.Append("'" + model.服务渠道 + "',");
            strSql.Append("'" + model.备注 + "',");
            strSql.Append("'" + model.档案修改时间 + "',");
            strSql.Append("'" + model.制表人 + "',");
            strSql.Append("'" + model.复核状态 + "'");
            strSql.Append(")");
            try
            {
                return OleDbHelper.ExecuteNonQuery(conn, strSql.ToString());
            }
            catch
            {
                return 0;
            }
        }

        public static int UpdateCustomerInfo(MiniCMS.Model.CustomerInfo model)
		{
			StringBuilder strSql=new StringBuilder();
			strSql.Append("update CustomerInfo set ");
			strSql.Append("客户姓名='"+model.客户姓名+"',");
			strSql.Append("性别='"+model.性别+"',");
			strSql.Append("出生日期='"+model.出生日期+"',");
			strSql.Append("联系电话='"+model.联系电话+"',");
			strSql.Append("手机号码='"+model.手机号码+"',");
			strSql.Append("联系地址='"+model.联系地址+"',");
			strSql.Append("学历='"+model.学历+"',");
			strSql.Append("职业='"+model.职业+"',");
			strSql.Append("开户日期='"+model.开户日期+"',");
			strSql.Append("注销日期='"+model.注销日期+"',");
			strSql.Append("Email='"+model.Email+"',");
			strSql.Append("QQ='"+model.QQ+"',");
			strSql.Append("MSN='"+model.MSN+"',");
			strSql.Append("投资品种='"+model.投资品种+"',");
			strSql.Append("交易类型='"+model.交易类型+"',");
			strSql.Append("交易风格='"+model.交易风格+"',");
			strSql.Append("持仓情况='"+model.持仓情况+"',");
			strSql.Append("连带账户='"+model.连带账户+"',");
			strSql.Append("家庭实际地址='"+model.家庭实际地址+"',");
			strSql.Append("家庭财产情况='"+model.家庭财产情况+"',");
			strSql.Append("家庭成员='"+model.家庭成员+"',");
			strSql.Append("性格特征='"+model.性格特征+"',");
			strSql.Append("爱好='"+model.爱好+"',");
			strSql.Append("客户需求='"+model.客户需求+"',");
			strSql.Append("理财意识、风险偏好='"+model.理财意识风险偏好+"',");
			strSql.Append("社会关系='"+model.社会关系+"',");
			strSql.Append("已经享受的服务='"+model.已经享受的服务+"',");
			strSql.Append("实际操作人='"+model.实际操作人+"',");
			strSql.Append("经纪人='"+model.经纪人+"',");
			strSql.Append("服务渠道='"+model.服务渠道+"',");
			strSql.Append("备注='"+model.备注+"',");
			strSql.Append("档案修改时间='"+model.档案修改时间+"',");
			strSql.Append("制表人='"+model.制表人+"',");
			strSql.Append("复核状态='"+model.复核状态+"'");
            strSql.Append(" where 客户编号=" + model.客户编号 + "");
            return OleDbHelper.ExecuteNonQuery(conn, strSql.ToString());
		}

        public static int CheckCustomerInfo(CustomerInfo model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("update CustomerInfo set ");
            strSql.Append("复核状态='" + model.复核状态 + "'");
            strSql.Append(" where 客户编号=" + model.客户编号 + "");
            return OleDbHelper.ExecuteNonQuery(conn, strSql.ToString());
        }

        public static List<CustomerInfo> ImportFromExcel(string fileName)
        {
            string connStr = "Provider=Microsoft.Jet.OLEDB.4.0; Extended Properties=\"Excel 8.0;HDR=No;IMEX=1\"; Data Source=" + fileName;

            string sql = "SELECT * FROM [Sheet1$]";
            var tb = OleDbHelper.ExecuteDataset(connStr, CommandType.Text, sql).Tables[0];
            for (int i = 0; i < tb.Columns.Count; i++)
            {
                tb.Columns[i].ColumnName = tb.Rows[0][i].ToString();
            }
            tb.Rows.RemoveAt(0);

            var list = DTOHelper.CreateModelList<CustomerInfo>(tb);

            return list;
        }

        #endregion
    }
}
